package com.glsc.ngateway.oaflow.service;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.glsc.ngateway.common.api.common.dto.PageDto;
import com.glsc.ngateway.common.api.common.dto.PageQueryParamDto;
import com.glsc.ngateway.common.api.common.enums.GatewayConstant;
import com.glsc.ngateway.common.api.ldap.dto.*;
import com.glsc.ngateway.common.api.ldap.feign.IFeignLdapService;
import com.glsc.ngateway.common.api.ldap.resp.AppEhrResponse;
import com.glsc.ngateway.common.api.ldap.resp.LdapResponse;
import com.glsc.ngateway.common.api.oaflow.dto.*;
import com.glsc.ngateway.common.api.oaflow.dto.oa.OaParaDto;
import com.glsc.ngateway.common.api.oaflow.dto.oaflow.*;
import com.glsc.ngateway.common.api.oaflow.dto.oaform.FormDictInfoDto;
import com.glsc.ngateway.common.api.oaflow.dto.oaform.FormInfoDto;
import com.glsc.ngateway.common.api.oaflow.dto.param.ParamFlowSearchDto;
import com.glsc.ngateway.common.api.oaflow.resp.OaflowResponse;
import com.glsc.ngateway.common.base.converter.oaflow.VITSystemOAFlowResourceConverter;
import com.glsc.ngateway.common.base.converter.oaflow.WorkflowBaseConverter;
import com.glsc.ngateway.common.base.domain.mysql.gateway.oaflow.*;
import com.glsc.ngateway.common.base.domain.oracle.ecology.*;
import com.glsc.ngateway.common.base.enums.OaWorkFlowRequestTableStatusEnum;
import com.glsc.ngateway.common.base.repo.mysql.gateway.oaflow.*;
import com.glsc.ngateway.common.base.repo.oracle.ecology.*;
import com.glsc.ngateway.oaflow.exception.OaflowException;
import com.glsc.ngateway.oaflow.utils.DictConstant;
import com.google.common.collect.Lists;
import lombok.Builder;
import lombok.Data;
import lombok.NonNull;
import org.apache.commons.lang.StringUtils;
import org.apache.logging.log4j.util.Strings;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import javax.transaction.Transactional;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;

@Component
public class FlowService {

    Logger logger = LoggerFactory.getLogger(FlowService.class);
    @Autowired
    TOaFlowMessageTableRepository tOaFlowMessageTableRepository;
    @Autowired
    TOaFlowSystemTableRepository tOaFlowSystemTableRepository;
    @Autowired
    TOaFlowRelativeMetaTableRepository tOaFlowRelativeMetaTableRepository;
    @Autowired
    TOaFlowMetaTableRepository tOaFlowMetaTableRepository;
    @Autowired
    WorkflowRequestlogRepository workflowRequestlogRepository;
    @Autowired
    WorkflowRequestbaseRepository workflowRequestbaseRepository;
    @Autowired
    WorkflowBillRepository workflowBillRepository;
    @Autowired
    OracleJdbcTemplate oracleJdbcTemplate;
    @Autowired
    private NamedParameterJdbcTemplate namedJdbcTemplateEcology;
    @Autowired
    HrmresourceRepository hrmresourceRepository;
    @Autowired
    HrmsubcompanyRepository hrmsubcompanyRepository;
    @Autowired
    HrmdepartmentRepository hrmdepartmentRepository;
    @Resource
    private IFeignLdapService iFeignLdapService;
    @Autowired
    AppendixService appendixService;
    @Autowired
    UFyyglzbszzszbRepository uFyyglzbszzszbRepository;
    @Autowired
    WorkflowNodeLinkRepository workflowNodeLinkRepository;
    @Autowired
    private WorkflowNodeBaseRepository workflowNodeBaseRepo;
    @Autowired
    private DocImageFileRepository docImageFileRepository;
    @Autowired
    private SystemConfigService systemConfigService;
    @Autowired
    VITSystemOAFlowRepository vitSystemOAFlowRepository;
    @Resource
    private WorkflowRequestbaseRepository workflowRequestbaseRepo;
    @Resource
    private WorkflowBaseRepository workflowBaseRepository;
    @Resource
    private TOaFlowAuthorityRepository oaFlowAuthorityRepository;

    public static final String APPENDIX_FILE_NAMES_FIELD = "appendix_file_names";

    public void addDigitalCertificate(UFyyglzbszzszb uFyyglzbszzszb) throws Exception {
        List<UFyyglzbszzszb> list = uFyyglzbszzszbRepository.findByDyptAndZsslh(uFyyglzbszzszb.getDypt(), uFyyglzbszzszb.getZsslh());
        if (list != null && list.size() > 0) {
            uFyyglzbszzszb.setId(list.get(0).getId());
        }
        uFyyglzbszzszb.setBgr(Integer.parseInt(getUserId(uFyyglzbszzszb.getBgraccount())));
        uFyyglzbszzszb.setSybm(Integer.parseInt(getDepartmentId(uFyyglzbszzszb.getSybmname())));
        uFyyglzbszzszbRepository.save(uFyyglzbszzszb);
    }

    public List<Map<String, String>> getRequestDataNew(String requestId, String status) throws Exception {
        logger.info("获取requestId={},status={}的元素数据开始", requestId, status);
        List<Map<String, String>> ret = getRequestInfoFlow(requestId, status);
        logger.info("获取requestId={},status={}的元素数据结束", requestId, status);
        return ret;
    }

    /**
     * 根据流程实例id返回流程类型workflowid
     *
     * @return 返回流程类型workflowId，如如相关信息返回0
     */
    public Integer getWorkflowIdByRequestId(Integer requestId) {
        WorkflowRequestbase requestBaseInfo = workflowRequestbaseRepo.findByRequestid(requestId).orElse(null);
        if (requestBaseInfo == null) {
            return 0;
        } else {
            return requestBaseInfo.getWorkflowid();
        }
    }

    /**
     * 根据流程实例id返回流程类型workflowid
     *
     * @return 返回流程类型workflowId，如如相关信息返回0
     */
    public Long getCurrentWorkflowIdByWorkflowId(Long workflowId) {
        WorkflowBase info = workflowBaseRepository.findOneById(workflowId);
        if (info == null || info.getActiveversionid() == null) {
            logger.error(workflowId+"查询不到最新id");
            return workflowId;
        } else {
            return Long.valueOf(info.getActiveversionid().toString());
        }
    }

    public int getWorkflowIdByRequestId(String requestId) {
        return this.getWorkflowIdByRequestId(Integer.parseInt(requestId));
    }

    /**
     * 查询OA流程实例基础信息
     *
     * @return 返回流程基础信息 如果未找到流程实例，返回null
     */
    public WorkflowRequestbase getRequestBaseInfo(int requestId) {
        WorkflowRequestbase requestBaseInfo = workflowRequestbaseRepo.findByRequestid(requestId).orElse(null);
        return requestBaseInfo;
    }

    public List<WorkflowRequestbase> getRequestBaseInfo(List<Integer> requestId) {
        return workflowRequestbaseRepo.findAllByRequestidIn(requestId);
    }

    public List<Map<String, String>> getRequestInfoFlow(String requestId, String flowStatus) throws Exception {
        WorkflowRequestbase workflow = workflowRequestbaseRepo.findByRequestid(Integer.valueOf(requestId)).orElseThrow(() -> new OaflowException("未找到workflow, requestId-" + requestId));
        String workflowId = String.valueOf(workflow.getWorkflowid());
        List<TOaFlowRelativeMetaTable> workFlowList = tOaFlowRelativeMetaTableRepository.findByWorkFlowId(workflowId);
        if (CollectionUtils.isEmpty(workFlowList)) {
            return Collections.EMPTY_LIST;
        }
        TOaFlowRelativeMetaTable t = workFlowList.get(0);
        //取得主表名和明细表名
        String table = t.getWorkFlowTable();
        String detailTable = t.getWorkFlowDetailTable();
        String detailMapConvertName = t.getDetailMapConvertName();
        //获取配置的数据元素对照和属性
        List<TOaFlowMetaTable> mapList = tOaFlowMetaTableRepository.getFlowMetaDataList(workflowId);
        //获取属性下拉框的属性集
        Map<String, Map<Integer, String>> selection = getSelectionNewByFlowId(workflowId);

        List<Map<String, String>> finalList = new ArrayList<>();
        finalList.add(getRequestInfo(workflow, Integer.valueOf(requestId), table, detailTable, mapList, selection, flowStatus, detailMapConvertName));
        return finalList;
    }

    private Map<String, Map<Integer, String>> getSelectionNewByFlowId(String workflowId) {
        String sql = "select a.ID as \"id\",lower(a.FIELDNAME) as \"fieldName\" " +
                " from ECOLOGY_TARGET.workflow_billfield a,ECOLOGY_TARGET.htmllabelinfo b,ECOLOGY_TARGET.workflow_bill c,ECOLOGY_TARGET.workflow_base d " +
                " where b.indexid=a.fieldlabel and c.id=a.billid " +
                " AND c.id=d.FORMID " +
                " AND d.ID = " + workflowId + " " +
                " AND b.LANGUAGEID = 7 ";

        List<Map<String, Object>> list = oracleJdbcTemplate.query(sql);
        logger.debug("获取下拉框选项getSelectionNew执行SQL为:" + sql);

        Map<String, Map<Integer, String>> finalMap = new HashMap<>();
        for (Map<String, Object> map : list) {
            String fieldName = map.get("fieldName").toString();
            String query = "select SELECTVALUE as \"value\", SELECTNAME as \"name\" from ECOLOGY_TARGET.WORKFLOW_SELECTITEM where FIELDID = " + "'" + map.get("id") + "'";
            List<Map<String, Object>> tmpList = oracleJdbcTemplate.query(query);
            logger.debug("获取下拉框选项getSelectionNew执行SQL为:" + query);

            Map<Integer, String> selection = new HashMap<>();
            for (Map<String, Object> tmpMap : tmpList) {
                selection.put(Integer.parseInt(tmpMap.get("value").toString()), tmpMap.get("name").toString());
            }
            if (CollectionUtil.isNotEmpty(selection)) {
                finalMap.put(fieldName, selection);
            }
        }
        return finalMap;
    }

    private Map<String, Map<Integer, String>> getSelectionNew(String table) {
        String sql = "select wf.ID as \"id\",lower(wf.FIELDNAME) as \"fieldName\" " +
                " from ECOLOGY_TARGET.WORKFLOW_BILL wb" +
                " Left join ECOLOGY_TARGET.workflow_billfield wf on wb.ID = wf.BILLID where wb.TABLENAME = lower(" + "'" + table + "')" +
                " and wf.FIELDHTMLTYPE = 5";

        List<Map<String, Object>> list = oracleJdbcTemplate.query(sql);
        logger.debug("获取下拉框选项getSelectionNew执行SQL为:" + sql);

        Map<String, Map<Integer, String>> finalMap = new HashMap<>();
        for (Map<String, Object> map : list) {
            String fieldName = map.get("fieldName").toString();
            String query = "select SELECTVALUE as \"value\", SELECTNAME as \"name\" from ECOLOGY_TARGET.WORKFLOW_SELECTITEM where FIELDID = " + "'" + map.get("id") + "'";
            List<Map<String, Object>> tmpList = oracleJdbcTemplate.query(query);
            logger.debug("获取下拉框选项getSelectionNew执行SQL为:" + query);

            Map<Integer, String> selection = new HashMap<>();
            for (Map<String, Object> tmpMap : tmpList) {
                selection.put(Integer.parseInt(tmpMap.get("value").toString()), tmpMap.get("name").toString());
            }
            finalMap.put(fieldName, selection);
        }
        return finalMap;
    }


    public Map getRequestInfo(@NonNull WorkflowRequestbase workflow, Integer requestId, String table, String detailTable, List<TOaFlowMetaTable> mapList, Map<String, Map<Integer, String>> selection, String flowStatus, String detailMapConvertName) throws Exception {
        //没传flowStatus这个参数也能过
        if ((flowStatus != null && !("").equals(flowStatus.trim())) && flowStatus.equals(workflow.getCurrentnodetype())) {//限定特定状态
            throw new OaflowException("requestId为：" + requestId + "的流程在OA尚未结束");
        }

        Map<String, Object> ret = new HashMap<String, Object>();
        ret.put(OaflowResponse.requestId, requestId);
        ret.put(OaflowResponse.flowStatusType, workflow.getCurrentnodetype());
        ret.put(OaflowResponse.flowNode, workflow.getStatus());
        ret.put(OaflowResponse.flowNodeCode, workflow.getCurrentnodeid());
        ret.put(OaflowResponse.workFlowName, workflow.getRequestnamenew());
        ret.put(OaflowResponse.workFlowId, workflow.getWorkflowid());
        ret.put(OaflowResponse.requestName, workflow.getRequestnamenew());
        if (CollectionUtil.isNotEmpty(mapList)) {
            ret.put(OaflowResponse.workFlowTypeName, mapList.get(0).getFlowName());//网关配置OA流程名称
        }

        //从OA获取主元素信息
        Map<String, Object> oaItems = getItemsFromOA(requestId, table.toUpperCase(), detailTable);

        StringBuffer sql = new StringBuffer();
        StringBuffer dsql = new StringBuffer();

        //替换下拉框的内容
        logger.info("进行下拉框内容转换，字典转换成value");
        //YWLS 0,1,2,3,4
        Map<String, Object> tmpselect = new HashMap<>();
        //ywlx 0-xxx 1-yyy
        for (Map.Entry<String, Map<Integer, String>> entry : selection.entrySet()) {
            //ywlx
            String oaItemkey = entry.getKey();
            //0-xxx 1-yyy
            Map<Integer, String> selectMap = entry.getValue();
            if (oaItems.containsKey(oaItemkey.toUpperCase())) {
                //元素属性是下拉框的
                if (oaItems.get(oaItemkey.toUpperCase()) != null) {
                    //对于多选值的处理
                    String selects[] =oaItems.get(oaItemkey.toUpperCase()).toString().split(",");
                    List<String> newValList = new ArrayList();
                    if (selects != null && selects.length > 0){
                        for (String select:selects) {
                            newValList.add(selectMap.get(Integer.parseInt(select)));
                        }
                    }
                    //取得下拉框 字典值 对应的 中文
                    String newVal = String.join(",",newValList);
//                    String newVal = selectMap.get(Integer.parseInt(oaItems.get(oaItemkey.toUpperCase()).toString()));
                    //同时存储对应的字典值
                    tmpselect.put(oaItemkey.toUpperCase(), oaItems.get(oaItemkey.toUpperCase()).toString());

                    oaItems.put(oaItemkey.toUpperCase(), newVal);
                }
            }
        }
        //字段名称转换
        logger.info("将OA字段名称转换为网关字段名称");
        Map<String, Object> afterCvtOaItems = new LinkedHashMap<String, Object>();
        List<TOaFlowMetaTable> metas = tOaFlowMetaTableRepository.findByWorkFlowId("" + workflow.getWorkflowid());
        if (CollectionUtils.isEmpty(metas)) {
            throw new OaflowException("没有获取到workflowId=" + workflow.getWorkflowid() + "的TOaFlowMetaTable表记录，请检查Mysql-TOaFlowMetaTable配置信息");
        }
        Map<String, String> convertMap = getConvertMap(workflow.getWorkflowid());
        convertMap.put("ID", "ID");
        convertMap.put("SQDH", "applyNumber");
        for (Map.Entry<String, Object> entry : oaItems.entrySet()) {
            String key = entry.getKey();
            Object val = entry.getValue();
            if (val == null) {
                continue;
            }
            if (convertMap.containsKey(key)) {
                afterCvtOaItems.put(convertMap.get(key), val);
                //如果是字典，那么也put一个字典
                if (tmpselect.containsKey(key)) {
                    afterCvtOaItems.put(convertMap.get(key) + "Code", tmpselect.get(key));
                }
            } else {
                logger.debug("TOaFlowMetaTable表中没有配置workflowId={}的{}字段转换配置，查看是不是必须配置。", workflow.getWorkflowid(), key);
//                afterCvtOaItems.put(key, val);
//                if (tmpselect.containsKey(key)) {
//                    afterCvtOaItems.put(key + "Code", tmpselect.get(key));
//                }
            }
        }

        if (afterCvtOaItems.size() <= 0) {
            throw new OaflowException("查询结果为NULL");
        }
        int id = Integer.parseInt(afterCvtOaItems.get("ID").toString());

        List<Map<String, Object>> detailRows = new ArrayList<>();
        if (!table.equals(detailTable)) {
            if (!detailTable.contains(",")) {
                dsql.append("select ");
                for (TOaFlowMetaTable map : mapList) {
                    if (map.getFieldType().equals("1"))
                        dsql.append(map.getFlowFieldName().toUpperCase() + " as \"" + map.getInterfaceFieldName() + "\",");
                }
                dsql.deleteCharAt(dsql.length() - 1);
                dsql.append(" from ECOLOGY_TARGET." + detailTable.toUpperCase() + " where MAINID=" + "'" + id + "'");

                logger.info(dsql.toString());

                detailRows = oracleJdbcTemplate.query(dsql.toString());
                //下拉框转换
                Map<String, String> mapRelation = mapList.stream().collect(Collectors.toMap(TOaFlowMetaTable::getInterfaceFieldName, TOaFlowMetaTable::getFlowFieldName, (old, cur) -> old));
                convertSelectItem(mapRelation, selection, detailRows);
                if (detailRows.size() > 0) {
                    String detailMapName = "detailMap";
                    if (StringUtils.isNotBlank(detailMapConvertName)) {
                        detailMapName = detailMapConvertName;
                    }
                    afterCvtOaItems.put(detailMapName, detailRows);

                }
            } else {
                //处理多个DT的情况
                String[] detailTables = detailTable.split(",");
                String[] detailConvertName = StringUtils.isNotBlank(detailMapConvertName) ? detailMapConvertName.split(",") : null;
                Map<String, String> mapRelation = mapList.stream().collect(Collectors.toMap(TOaFlowMetaTable::getInterfaceFieldName, TOaFlowMetaTable::getFlowFieldName, (old, cur) -> old));
                for (int i = 0; i < detailTables.length; i++) {
                    String de = detailTables[i];
                    String convertName = detailConvertName != null ? detailConvertName[i] : null;
                    //取得DT几
                    String index = de.substring(de.length() - 1);
                    //取得DT表名
                    StringBuffer ddsql = new StringBuffer();
                    ddsql.append("select ");
                    for (TOaFlowMetaTable map : mapList) {
                        if ((map.getFieldType().equals("1")) && (index.equals(map.getDetailIndex()))) {
                            ddsql.append(map.getFlowFieldName().toUpperCase() + " as \"" + map.getInterfaceFieldName() + "\",");
                        }
                    }
                    ddsql.deleteCharAt(ddsql.length() - 1);
                    ddsql.append(" from ECOLOGY_TARGET." + de.toUpperCase() + " where MAINID=" + "'" + id + "'");

                    logger.info(ddsql.toString());

                    detailRows = oracleJdbcTemplate.query(ddsql.toString());
                    //下拉框转换
                    convertSelectItem(mapRelation, selection, detailRows);
                    if (detailRows.size() > 0) {
                        String detailMapName = "detailMap" + index;
                        if (StringUtils.isNotBlank(detailMapConvertName)) {
                            detailMapName = convertName;
                        }
                        afterCvtOaItems.put(detailMapName, detailRows);
                    }
                }
            }
        }

        String nameAndEmail = getAppName(afterCvtOaItems.get("applicant").toString());
        afterCvtOaItems.put("applicant", nameAndEmail);

        if (nameAndEmail != null && nameAndEmail.contains(",") && nameAndEmail.contains("@")) {
            afterCvtOaItems.put("applicantAccount", nameAndEmail.split(",")[1].split("@")[0]);
        }

        afterCvtOaItems.put("applicantDepartId", afterCvtOaItems.get("applicantDepart").toString());
        afterCvtOaItems.put("applicantCompId", afterCvtOaItems.getOrDefault("applicantComp", Strings.EMPTY).toString());

        afterCvtOaItems.put("applicantDepart", getDepart(afterCvtOaItems.get("applicantDepart").toString()));
        afterCvtOaItems.put("applicantComp", getComp(afterCvtOaItems.getOrDefault("applicantComp", Strings.EMPTY).toString()));

//        if(rows.get("appendix") != null) {
//            rows.put("appendix", getDoc(rows.get("appendix").toString()));
//        }

        for (TOaFlowMetaTable map : mapList) {
            String interfaceFieldName = map.getInterfaceFieldName();
            if (interfaceFieldName.equals("applicant") || interfaceFieldName.equals("applicantDepart") || interfaceFieldName.equals("applicantComp")) {
                continue;
            }
            if (map.getFieldType().equals("0")) {
                fieldNameMapping(afterCvtOaItems, map, selection);
            } else if (afterCvtOaItems.containsKey("detailMap") && map.getFieldType().equals("1")) {
                for (Map dRows : detailRows) {
                    fieldNameMapping(dRows, map, selection);
                }
            } else if (!CollectionUtils.isEmpty(getDetailMapName(afterCvtOaItems))) {
                for (String s : getDetailMapName(afterCvtOaItems)) {
                    //detailMap1 contains 1,2,3
                    if (s.contains(map.getDetailIndex())) {
                        for (Map dRows : (List<Map<String, Object>>) afterCvtOaItems.get(s)) {
                            fieldNameMapping(dRows, map, selection);
                        }
                    }
                }
            }
        }
        ret.put("flowInfos", afterCvtOaItems);
        return ret;
    }

    private void convertSelectItem(Map<String, String> mapRelation, Map<String, Map<Integer, String>> selection, List<Map<String, Object>> detailRows) {
        try {
            if (CollectionUtil.isNotEmpty(mapRelation) && CollectionUtil.isNotEmpty(selection) && CollectionUtil.isNotEmpty(detailRows)) {
                List<Map<String, Object>> detailRowsNew = new ArrayList<>();
                for (Object o : detailRows) {
                    Map<String, String> m = (Map<String, String>) o;
                    Map<String, Object> newMap = new HashMap<>();
                    newMap.putAll(m);
                    Iterator<Map.Entry<String, String>> it = m.entrySet().iterator();
                    while (it.hasNext()) {
                        Map.Entry<String, String> entry = it.next();
                        String key = entry.getKey();
                        if (selection.keySet().contains(mapRelation.get(key))) {
                            Map<Integer, String> dic = selection.get(mapRelation.get(key));
                            Integer intValue = Integer.valueOf(String.valueOf(m.get(key)));
                            String value = dic.get(intValue);
                            newMap.put(key + "SelectName", value);
                        }
                    }
                    detailRowsNew.add(newMap);
                }
                detailRows.clear();
                detailRows.addAll(detailRowsNew);
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("转换下拉值错误", e);
        }

    }

    private Set<String> getDetailMapName(Map<String, Object> afterCvtOaItems) {
        Set<String> detailMapIndex = new HashSet<>();
        for (Map.Entry<String, Object> map : afterCvtOaItems.entrySet()) {
            if (map.getKey().startsWith("detailMap")) {
                detailMapIndex.add(map.getKey());
            }
        }
        return detailMapIndex;
    }

    /**
     * 获取OA中表单fieldname和网关系统对外提供的fieldname对应关系
     */
    private Map<String, String> getConvertMap(Integer workfolwId) {
        List<TOaFlowMetaTable> metas = tOaFlowMetaTableRepository.findByWorkFlowId("" + workfolwId);
        Map<String, String> ret = new HashMap<String, String>();
        for (TOaFlowMetaTable meta : metas) {
            ret.put(meta.getFlowFieldName().toUpperCase(), meta.getInterfaceFieldName());
        }
        return ret;
    }

    private Map<String, Object> getItemsFromOA(Integer requestId, String oatable, String oadetailTable) {
        logger.info("从OA获取主元素信息开始:requestId={},oatable={},oadetailTable={}", requestId, oatable, oadetailTable);
        //从OA读取流程表单元素信息
        List<Map<String, Object>> oaitems = oracleJdbcTemplate.query("select * from ECOLOGY_TARGET." + oatable + " where requestid=" + requestId);
        if (CollectionUtils.isEmpty(oaitems)) {
            throw new OaflowException("没有获取到requestId=" + requestId + "的" + oatable + "表记录");
        }
        logger.info("从OA获取主元素信息结束:{}", oaitems.get(0).toString());
        return oaitems.get(0);
    }

    public void fieldNameMapping(Map<String, Object> rows, TOaFlowMetaTable map, Map<String, Map<Integer, String>> selection) throws Exception {
        String interfaceFieldName = map.getInterfaceFieldName();
        String flowFieldName = map.getFlowFieldName();

        if (map.getIsPerson().equals("1") && rows.get(interfaceFieldName) != null) {
            if (interfaceFieldName.endsWith("Department")) {
                rows.put(interfaceFieldName + "Id", rows.get(interfaceFieldName).toString());
                rows.put(interfaceFieldName, getDepart(rows.get(interfaceFieldName).toString()));
            } else if (interfaceFieldName.endsWith("Member")) {
                if (rows.get(interfaceFieldName) != null) {
                    List<Map<String, Object>> list = getMemberInfo(rows.get(interfaceFieldName).toString());
                    if (list != null && list.size() > 0) {
                        rows.put(interfaceFieldName + "Id", list.get(0).get("hybm"));
                        rows.put(interfaceFieldName, list.get(0).get("hyjc"));
                    }
                }
            } else {
                rows.put(interfaceFieldName + "Id", rows.get(interfaceFieldName).toString());
                rows.put(interfaceFieldName + "Account", getAccount(rows.get(interfaceFieldName).toString()));
                rows.put(interfaceFieldName, getName(rows.get(interfaceFieldName).toString()));
            }
        }
        if (map.getIsDoc().equals("1") && rows.get(interfaceFieldName) != null) {
            List<DocFileInfo> docFileInfoList = Arrays.stream(rows.get(interfaceFieldName).toString().split(","))
                    .map(id -> {
                        String[] appendix = appendixService.callAppendixService(id);
                        return Objects.isNull(appendix) ? null :
                                DocFileInfo.builder()
                                        .id(id)
                                        .url(appendix[0])
                                        .fileName(appendix[1])
                                        .build();
                    })
                    .filter(Objects::nonNull)
                    .collect(Collectors.toList());
            //urlStr
            rows.put(interfaceFieldName, docFileInfoList.stream().map(DocFileInfo::getUrl).collect(Collectors.joining(",")));
            rows.put(interfaceFieldName + "FileInfo", docFileInfoList);
            //filenameStr
            rows.put(APPENDIX_FILE_NAMES_FIELD, docFileInfoList.stream().map(DocFileInfo::getFileName).collect(Collectors.joining(",")));
        }

    }

    public PageDto<WorkflowBaseDto> pageWorkflowBase(PageQueryParamDto<WorkflowBaseQueryParamDto> param) {
        param.setParam(Objects.isNull(param.getParam()) ? new WorkflowBaseQueryParamDto() : param.getParam());
        Pageable pageable = param.getPageable();
        Page<WorkflowBase> pageRes = workflowBaseRepository.page(pageable, param.getParam());
        List<WorkflowBaseDto> dto = pageRes.getContent().stream().map(WorkflowBaseConverter::do2Dto).collect(Collectors.toList());
        return new PageDto<>(dto, pageable.getPageNumber(), pageable.getPageSize(), pageRes.getTotalElements());
    }

    public Object getCustomRateChange(String flowtype, String applyDateBegin, String applyDateEnd) {
        String config = systemConfigService.findConfigValueByCode(DictConstant.CUSTOM_RATE_CHANGE);
        JSONObject configObject = JSONObject.parseObject(config);
        String workflowid = configObject.getString(flowtype.toUpperCase());
//        {"A":"338104","B":"337103"}
        Assert.notNull(workflowid, "类型错误");
        String sql = "SELECT x.REQUESTID ,x.STATUS ,x.CURRENTNODETYPE,x.CREATEDATE  FROM ECOLOGY_TARGET.WORKFLOW_REQUESTBASE x" +
                " WHERE" +
                " x.WORKFLOWID = " + workflowid ;
        List argList = new ArrayList();
        if (applyDateBegin != null) {
            argList.add(applyDateBegin);
            sql += " AND x.CREATEDATE >= ? ";
        }
        if (applyDateEnd != null) {
            argList.add(applyDateEnd);
            sql += " AND x.CREATEDATE <= ? ";
        }
        sql += " ORDER BY x.CREATEDATE DESC";
        List<Map<String, Object>> list = oracleJdbcTemplate.queryPre(sql, argList.toArray());
        return list;
    }

    @Builder
    @Data
    public static class DocFileInfo {
        private String id;
        private String url;
        private String fileName;
    }

    private String getName(String appId) {
        List<Integer> ids = Arrays.stream(appId.split(",")).map(Integer::parseInt).collect(Collectors.toList());
        LdapResponse<List<LdapEhrEmpDto>> resp = iFeignLdapService.getUserList(LdapEhrEmpSearchDto.builder().oaUserIdList(ids).build());
        if (Objects.isNull(resp) || CollectionUtil.isEmpty(resp.getData())) {
            logger.info("从ldap表中获取相关人员信息:appId={}, 未获取到人员信息", appId);
            return Strings.EMPTY;
        }
        String nameStr = resp.getData().stream().map(LdapEhrEmpDto::getName).filter(Strings::isNotBlank).collect(Collectors.joining(","));
        logger.info("从ldap表中获取相关人员信息:appId={}, 转换后(去掉逗号前):{}", appId, nameStr);
        return nameStr;
    }

    private String getAccount(String appId) {
        List<Integer> ids = Arrays.stream(appId.split(",")).map(Integer::parseInt).collect(Collectors.toList());
        LdapResponse<List<LdapEhrEmpDto>> resp = iFeignLdapService.getUserList(LdapEhrEmpSearchDto.builder().oaUserIdList(ids).build());
        if (Objects.isNull(resp) || CollectionUtil.isEmpty(resp.getData())) {
            logger.error("从ldap表中获取相关人员信息:appId={}, 未获取到人员信息", appId);
            return Strings.EMPTY;
        }
        String nameStr = resp.getData().stream().map(LdapEhrEmpDto::getBadge).filter(Strings::isNotBlank).collect(Collectors.joining(","));
        logger.info("从ldap表中获取相关人员信息:appId={}, 转换后(去掉逗号前):{}", appId, nameStr);
        return nameStr;
    }

    private String getDepart(String depId) throws Exception {
        List<Integer> ids = Arrays.stream(depId.split(",")).map(Integer::parseInt).collect(Collectors.toList());
        LdapResponse<List<LdapEhrDeptDto>> resp = iFeignLdapService.getDepartmentList(LdapEhrDeptSearchDto.builder().oaDeptIdList(ids).build());
        if (Objects.isNull(resp) || CollectionUtil.isEmpty(resp.getData())) {
            logger.error("从ldap表中获取申请部门信息:depId={}, 未获取到部门信息", depId);
            return Strings.EMPTY;
        }
        String nameStr = resp.getData().stream().map(LdapEhrDeptDto::getDtitle).collect(Collectors.joining(","));
        logger.info("从ldap表中获取申请部门信息:depId={}, 转换后(去掉逗号前):{}", depId, nameStr);
        return nameStr;
    }

    private String getComp(String compId) {
        if (Strings.isBlank(compId)) {
            return Strings.EMPTY;
        }
        LdapResponse<List<LdapEhrCompDto>> resp = iFeignLdapService.getCompanyList(LdapEhrCompSearchDto.builder().oaCompId(Integer.parseInt(compId)).build());
        if (Objects.isNull(resp) || CollectionUtil.isEmpty(resp.getData())) {
            logger.error("从ldap表中获取申请公司信息:depId={}, 未获取到公司信息", compId);
            return Strings.EMPTY;
        }
        String result = resp.getData().get(0).getSubcompanyname();
        logger.info("从hrmsubcompany表中获取申请子公司信息:compId{},转换后:{}", compId, result);
        return result;
    }

    public String getAppName(String appId) {
        LdapResponse<List<LdapEhrEmpDto>> resp = iFeignLdapService.getUserList(LdapEhrEmpSearchDto.builder().oaUserId(Integer.valueOf(appId)).build());
        if (Objects.isNull(resp) || CollectionUtil.isEmpty(resp.getData())) {
            logger.error("从ldap表中获取相关人员信息:appId={}, 未获取到人员信息", appId);
            return Strings.EMPTY;
        }
        LdapEhrEmpDto emp = resp.getData().get(0);
        String name = emp.getName() + "," + emp.getEmail();
        logger.info("从hrmresource表中获取申请人信息:appId={},转换后:{}", appId, name);
        return name;
    }

    private List<Map<String, Object>> getMemberInfo(String id) {
        if (StringUtils.isEmpty(id)) {
            return null;
        }
        String sql = "select hybm,hyjc from ECOLOGY_TARGET.UF_JYDYCZMD where id = '" + id + "' ";
        List<Map<String, Object>> items = oracleJdbcTemplate.query(sql);
        if (CollectionUtils.isEmpty(items)) {
            logger.error("未找到对应的会员id=" + id);
            return null;
        }
        return items;
    }

    public String getUserId(String account) {
        List<String> accounts = List.of(account.split(","));
        AppEhrResponse<List<LdapEhrEmpDto>> resp = iFeignLdapService.getEmpsByAccounts(accounts);
        if (Objects.isNull(resp) || !resp.isSuccess() || CollectionUtil.isEmpty(resp.getData())) {
            logger.info("从ldap表中获取相关人员信息未找到userid，返回account:account=" + account);
            return account;
        }
        logger.info("从ldap表中获取相关人员信息:account=" + account);
        return resp.getData().stream().filter(e -> Objects.nonNull(e.getOaUserId())).map(e -> String.valueOf(e.getOaUserId())).collect(Collectors.joining(","));
    }

    public String getDepartmentId(String departmentName) throws Exception {
        List<String> departmentNames = List.of(departmentName.split(","));
        LdapResponse<List<LdapEhrDeptDto>> resp = iFeignLdapService.getDepartmentList(LdapEhrDeptSearchDto.builder().dTitleList(departmentNames).build());
        if (Objects.isNull(resp) || CollectionUtil.isEmpty(resp.getData())) {
            logger.info("从ldap表中获取相关部门信息未找到id，departmentName:departmentName=" + departmentName);
            return Strings.EMPTY;
        }
        String str = resp.getData().stream().filter(d -> Objects.nonNull(d.getOaDeptId())).map(d -> String.valueOf(d.getOaDeptId())).collect(Collectors.joining(","));
        logger.info("从ldap表中获取相关部门信息:department=" + str);
        return str;
    }

    public Boolean insertOaMessage(TOaFlowMessageTable ofm) {
        TOaFlowMessageTable t = tOaFlowMessageTableRepository.save(ofm);
        if (t != null) {
            return true;
        } else {
            return false;
        }
    }

    public List<Map<String, Object>> getWorkflowBase(String workflowName) {
        String sql = "select id,workflowname,docpath,editdate,edittime from ECOLOGY_TARGET.WORKFLOW_BASE where workflowname like '%" + workflowName + "%' " +
                " and workflowtype in( " +
                " SELECT id from ECOLOGY_TARGET.WORKFLOW_TYPE WHERE typename like '%资产管理部%' OR typename = '运营管理' OR typename = '资管业务' ) ";
        if (StringUtils.isEmpty(workflowName)) {
            sql = "select id,workflowname,docpath,editdate,edittime from ECOLOGY_TARGET.WORKFLOW_BASE where " +
                    " workflowtype in( " +
                    " SELECT id from ECOLOGY_TARGET.WORKFLOW_TYPE WHERE typename like '%资产管理部%' OR typename = '运营管理' OR typename = '资管业务' ) ";
        }

        List<Map<String, Object>> items = oracleJdbcTemplate.query(sql);
        if (CollectionUtils.isEmpty(items)) {
//            throw new OaflowException("没有获取到workflowname=" + workflowName + "表记录");
            return null;
        }
        return items;
    }

    public List<Map<String, Object>> getWorkflowRequestBase(String beginDate, String endDate, String workflowId) {
        String sql = "select creater,requestid,workflowid,currentnodetype,status,requestname,requestnamenew,createdate,createtime,requestmark " +
                "from ECOLOGY_TARGET.WORKFLOW_REQUESTBASE WHERE CREATEDATE BETWEEN '" + beginDate + "' AND '" + endDate + "' and workflowid = '" + workflowId + "' ";
        if (StringUtils.isEmpty(workflowId)) {
            sql = "select creater,requestid,workflowid,currentnodetype,status,requestname,requestnamenew,createdate,createtime,requestmark " +
                    "from ECOLOGY_TARGET.WORKFLOW_REQUESTBASE WHERE CREATEDATE BETWEEN '" + beginDate + "' AND '" + endDate + "' ";
        }

        List<Map<String, Object>> items = oracleJdbcTemplate.query(sql);
        if (CollectionUtils.isEmpty(items)) {
//            throw new OaflowException("没有获取到workflowname=" + workflowName + "表记录");
            return null;
        }

        for (Map map : items) {
            if (map.get("CREATER") != null) {
                String account = getAppName(map.get("CREATER").toString());
                map.put("CREATERACCOUNT", account);
            }
        }
        return items;
    }

    //**********************************************以下方法待测试********************************************************
    public Boolean hasWorkFlowRequestBase(List<Integer> requestIds){
        List<WorkflowRequestbase> wfbaseList = workflowRequestbaseRepository.findAllByRequestidIn(requestIds);
        if (CollectionUtil.isEmpty(wfbaseList)) {
            logger.error("无workflowrequestbase结果{}",requestIds);
            return false;
        }
        List<WorkflowRequestlog> reqLogs = workflowRequestlogRepository.findAllByRequestidIn(Lists.transform(requestIds,  Integer::longValue));
        if (CollectionUtils.isEmpty(reqLogs)) {
            logger.error("无workflowrequestlog结果{}",requestIds);
            return false;
//            throw new OaflowException("没有获取到requestId=" + requestIds + "的WorkflowRequestlog记录");
        }
        return true;
    }

    public List<Map<String, Object>> getWorkFlowCurrentStatus(List<Integer> requestIds) {
        logger.info("开始获取requestId={}的当前状态", requestIds);

        List<WorkflowRequestbase> wfbaseList = workflowRequestbaseRepository.findAllByRequestidIn(requestIds);
        if (CollectionUtil.isEmpty(wfbaseList)) {
            throw new OaflowException("没有获取到requestId=" + requestIds + "的WorkflowRequestbase记录");
        }
        return wfbaseList.stream()
                .map(wfbase -> new HashMap<String, Object>() {{
                    put(OaflowResponse.requestStatus, wfbase.getCurrentnodetype());
                    put(OaflowResponse.requestlogType, GatewayConstant.FLOW_STATUS_MAP.get(wfbase.getCurrentnodetype()));
                    put("baseStatus", wfbase.getStatus());
                    put("baseCurrentNodeType", wfbase.getCurrentnodetype());
                }})
                .collect(Collectors.toList());
    }

    public List<Map<String, Object>> getWorkFlowInfos(List<Long> requestIds) {
        logger.info("开始获取requestId={}的相关流程log", requestIds);
        List<WorkflowRequestbase> wfbaseList = workflowRequestbaseRepository.findAllById(requestIds.stream().map(id -> Integer.valueOf(id.toString())).collect(Collectors.toList()));
        if (CollectionUtil.isEmpty(wfbaseList)) {
            throw new OaflowException("没有获取到requestId=" + requestIds + "的WorkflowRequestbase记录");
        }
//        requestIds.stream().map(id -> Long.valueOf(id.toString())).collect(Collectors.toList());
        List<WorkflowRequestlog> reqLogs = workflowRequestlogRepository.findAllByRequestidIn(requestIds);
        if (CollectionUtils.isEmpty(reqLogs)) {
            throw new OaflowException("没有获取到requestId=" + requestIds + "的WorkflowRequestlog记录");
        }
        Map<Long, List<WorkflowRequestlog>> reqMap = reqLogs.stream().collect(Collectors.groupingBy(WorkflowRequestlog::getRequestid));
        return wfbaseList.stream()
                .map(wfbase -> {
                            Map<String, Object> data = new HashMap<>();
                            long reqId = Long.parseLong(wfbase.getRequestid().toString());
                            data.put(OaflowResponse.requestId, reqId);
                            data.put(OaflowResponse.requestName, wfbase.getRequestname());
                            List<WorkflowRequestlog> flowinfos = Optional.ofNullable(reqMap.get(reqId))
                                    .map(m -> m.stream()
                                            .filter(log -> Objects.nonNull(log.getHrmresource()) && Strings.isNotBlank(log.getHrmresource().getLoginid()))
                                            .collect(Collectors.toList()))
                                    .orElse(new ArrayList<>(0));
                            data.put(OaflowResponse.flowInfos, flowinfos);
                            return data;
                        }
                )
                .collect(Collectors.toList());
    }

    public List<TOaFlowSystemTable> getRecvSystem(String workflowId) {
        return tOaFlowSystemTableRepository.getSystemName(workflowId);
    }

    public FlowDetailFrontDto getWorkFlowOaPicData(Integer requestId) {
        //查询workflow_requestbase，获取workflowid,workflowname
        Optional<WorkflowRequestbase> wfbase = workflowRequestbaseRepository.findById(requestId);
        if (!wfbase.isPresent()) {
            throw new OaflowException("没有获取到requestId=" + requestId + "的WorkflowRequestbase记录");
        }
        WorkflowRequestbase workflowBase = wfbase.get();
        String sqlWfBase = "select workflowname from ECOLOGY_TARGET.workflow_base where id = " + workflowBase.getWorkflowid();
        List<Map<String, Object>> wfNameRes = oracleJdbcTemplate.query(sqlWfBase);
        //查询 workflow_nodelink 获取节点连接信息
        List<WorkFlowNodeLink> linkList = workflowNodeLinkRepository.findByWorkflowid(workflowBase.getWorkflowid());
        List<FrontFlowEdge> edges = linkList.stream().map(e -> {
            FrontFlowEdge edge = new FrontFlowEdge();
            BeanUtil.copyProperties(e, edge);
            List<FrontFlowEdgeFoldPoint> frontFlowEdgeFoldPoints = string2PointList(e.getPoints());
            List<FrontFlowEdgeFoldPoint> foldPointList = frontFlowEdgeFoldPoints.subList(1, frontFlowEdgeFoldPoints.size() - 1);
            edge.setFoldPointList(foldPointList);
            return edge;
        }).collect(Collectors.toList());
        //关联查询，获取流程节点信息
        String sql = "select nodeid,nodetype, nodename, username,xpos,ypos, account, operatedate, operatetime, curoper, noopergroupname, currentnodeid " +
                "from (select nodeid,nodetype, nodeorder, currentnodeid, nodename,xpos,ypos, opergroupid, operuserid, operatedate, operatetime, curoper, username, account, noopergroupname, row_number() over (partition by nodeid,operuserid,opergroupid order by nodeorder ) row_id " +
                "from (select distinct wfreq.requestid as requestid, wfreq.workflowid as wfid, wfreq.currentnodeid as currentnodeid, wfnode.NODETYPE as nodetype, wfnode.nodeid as nodeid, wfnode.nodeorder as nodeorder, wfnb.nodename as nodename,wfnb.DRAWXPOS as xpos,wfnb.DRAWYPOS as ypos, wfcoper.groupid as opergroupid, wfcoper.userid as operuserid, wfcoper.operatedate as operatedate, wfcoper.operatetime as operatetime, wfrl.OPERATEDATE as curoper, hruser.accountname as username, hruser.loginid as account, wfng.groupname as noopergroupname " +
                "from ECOLOGY_TARGET.workflow_requestbase wfreq left join ECOLOGY_TARGET.workflow_flownode wfnode on wfreq.workflowid = wfnode.workflowid left join ECOLOGY_TARGET.workflow_nodebase wfnb on wfnode.nodeid = wfnb.id left join ECOLOGY_TARGET.workflow_nodegroup wfng on wfng.nodeid = wfnb.id left join ECOLOGY_TARGET.workflow_currentoperator wfcoper on wfcoper.nodeid = wfnb.id and wfcoper.requestid = wfreq.requestid and wfcoper.workflowid = wfreq.workflowid left join ECOLOGY_TARGET.hrmresource hruser on hruser.id = wfcoper.userid " +
                " left join ECOLOGY_TARGET.workflow_requestlog wfrl on wfrl.OPERATOR = wfcoper.userid and wfrl.NODEID =wfnb.id and wfrl.requestid = wfreq.requestid where wfreq.requestid = " + requestId + ")) a where row_id = 1 or operuserid is null order by nodeorder";
        List<Map<String, Object>> list = oracleJdbcTemplate.query(sql);
        List<WorkflowNode> nodeList = list.stream().map(e -> JSON.parseObject(JSON.toJSONString(e), WorkflowNode.class)).collect(Collectors.toList());

        FlowDetailFrontDto resDto = new FlowDetailFrontDto();
        resDto.setFlowName(wfNameRes.get(0).get("workflowname").toString());
        resDto.setFlowDefinitionKey(workflowBase.getRequestid().toString());
        resDto.setEdges(edges);
        //遍历组织节点信息
        List<FrontFlowNode> resNodeList = new ArrayList<>();
        Map<String, FrontFlowNode> nodeMap = new HashMap<>();
        for (WorkflowNode node : nodeList) {
            FrontFlowNode resNode;
            if (nodeMap.keySet().contains(node.getNodeId())) {
                resNode = nodeMap.get(node.getNodeId());
            } else {
                resNode = new FrontFlowNode();
                resNode.setNodeTaskKey(node.getNodeId());
                resNode.setNodeTaskName(node.getNodeName());
                if (StringUtils.equalsIgnoreCase(workflowBase.getCurrentnodeid().toString(), node.getNodeId())) {// active : 1
                    resNode.setNodeTaskStatus("1");
                } else {
                    resNode.setNodeTaskStatus(StringUtils.isEmpty(node.getUserName()) ? "2" : "0");
                }
                resNode.setNodeTaskType(GatewayConstant.NODE_TYPE_MAP.get(node.getNodeType()));
            }
            List<NodeTaskAssignees> resNodeAssList;
            NodeTaskAssignees assignees = new NodeTaskAssignees();
            ;
            if (CollectionUtil.isEmpty(resNode.getAssignees())) {
                resNodeAssList = new ArrayList<>();
            } else {
                resNodeAssList = resNode.getAssignees();
            }
            assignees.setAccount(StringUtils.isEmpty(node.getAccount()) ? null : node.getAccount());
            assignees.setName(StringUtils.isEmpty(node.getUserName()) ? node.getNoOpergroupName() : node.getUserName());
            assignees.setTime(StringUtils.isEmpty(node.getOperatedate()) ? null : node.getOperatedate() + node.getOperatetime());
            assignees.setStatus(StringUtils.isEmpty(node.getCuroper()) ? "0" : "1");
            resNodeAssList.add(assignees);
            resNode.setAssignees(resNodeAssList);
            resNode.setNodeX(Double.valueOf(node.getXpos()));
            resNode.setNodeY(Double.valueOf(node.getYpos()));
            resNodeList.add(resNode);
        }
        resDto.setNodes(resNodeList);
        return resDto;
    }

    private List<FrontFlowEdgeFoldPoint> string2PointList(String str) {
        String[] split = str.split(",");
        StringBuilder sb = new StringBuilder("[");
        for (int i = 0; i < split.length; i++) {
            if (i % 2 == 0) {
                sb.append("{\"x\":").append(split[i]).append(",");
            } else {
                sb.append("\"y\":").append(split[i]).append("},");
            }
        }
        StringBuilder stringBuilder = sb.deleteCharAt(sb.length() - 1);
        stringBuilder.append("]");
        return JSON.parseArray(stringBuilder.toString(), FrontFlowEdgeFoldPoint.class);
    }

    public List<FormInfoDto> getWorkFlowOaFormInfo(String workflowId) {
        if (StringUtils.isBlank(workflowId)) {
            throw new OaflowException("请求参数workflowId为空！");
        }
        String sql = "select a.id AS fieldId, a.fieldname AS fieldName, a.fieldlabel AS fieldLabel, b.labelname AS fieldLabelName, a.FIELDHTMLTYPE AS fieldHtmlType, " +
                "a.DSPORDER AS fieldDspOrder, c.tablename AS fieldTableName, a.detailtable AS fieldDetailTableName, b.indexid AS indexId, d.id AS workflowId, d.FORMID formId " +
                "from ECOLOGY_TARGET.workflow_billfield a, ECOLOGY_TARGET.htmllabelinfo b, ECOLOGY_TARGET.workflow_bill c, ECOLOGY_TARGET.workflow_base d where b.indexid = a.fieldlabel and c.id = a.billid AND c.id = d.FORMID AND b.LANGUAGEID = 7 " +
                "AND d.ID = " + workflowId + " ORDER BY c.TABLENAME, a.DETAILTABLE, a.FIELDNAME";
        List<Map<String, Object>> list = oracleJdbcTemplate.query(sql);
        if (CollectionUtil.isEmpty(list)) {
            return null;
        }
        List<FormInfoDto> formInfoList = list.stream().map(e -> JSON.parseObject(JSON.toJSONString(e), FormInfoDto.class)).collect(Collectors.toList());
        return formInfoList;
    }

    public List<FormDictInfoDto> getWorkFlowOaFormDictInfo(String workflowId) {
        if (StringUtils.isBlank(workflowId)) {
            throw new OaflowException("请求参数workflowId为空！");
        }
        String sql = "select c.id AS id, c.FIELDID AS fieldId, a.fieldname AS fieldName, b.labelname AS fieldLabelName, c.SELECTNAME AS dictSelectName, c.SELECTVALUE AS dictSelectValue " +
                "from ECOLOGY_TARGET.workflow_billfield a, ECOLOGY_TARGET.htmllabelinfo b, ECOLOGY_TARGET.workflow_selectItem c, ECOLOGY_TARGET.workflow_base d where a.id = c.FIELDID AND d.ID = " + workflowId + " AND a.billid = d.FORMID " +
                "AND b.indexid = a.fieldlabel AND b.LANGUAGEID = 7 order by fieldid, selectvalue";
        List<Map<String, Object>> list = oracleJdbcTemplate.query(sql);
        if (CollectionUtil.isEmpty(list)) {
            return null;
        }
        List<FormDictInfoDto> formDictInfoList = list.stream().map(e -> JSON.parseObject(JSON.toJSONString(e), FormDictInfoDto.class)).collect(Collectors.toList());
        return formDictInfoList;
    }

    public List<String> getWorkFlowOaMainTableField(Integer requestId) {
        String sql1 = " select a.tablename from ECOLOGY_TARGET.workflow_bill a, ECOLOGY_TARGET.workflow_base b, ECOLOGY_TARGET.WORKFLOW_REQUESTBASE c " +
                "where a.id = b.FORMID AND c.WORKFLOWID = b.ID AND c.REQUESTID = " + requestId;
        String table = oracleJdbcTemplate.queryString(sql1);
        if (StringUtils.isBlank(table)) {
            return new ArrayList<>();
        }
        String sql2 = "select * from ECOLOGY_TARGET." + table + " where REQUESTID = " + requestId;
        List<Map<String, Object>> list = oracleJdbcTemplate.query(sql2);
        if (CollectionUtil.isNotEmpty(list)) {
            return CollectionUtil.newArrayList(list.get(0).keySet());
        }
        return new ArrayList<>();
    }

    public List<VITSystemOAFlowResourceDto> getOAFlowList(ParamFlowSearchDto params) {
        return VITSystemOAFlowResourceConverter.do2Dto(vitSystemOAFlowRepository.queryAll(params));
    }

    //系统关联流程统计
    public SystemFlowStatisticsDto getOAFlowStatistics(SystemFlowStatisticsSearchParamDto params) {
        MapSqlParameterSource paramMap = new MapSqlParameterSource();
        paramMap.addValue("systemClassNoList", params.getSystemClassNoList());
        paramMap.addValue("beginDate", params.getBeginDate());
        paramMap.addValue("endDate", params.getEndDate());

        int size = params.getSystemClassNoList().size();
        SystemFlowStatisticsDto resultDto = new SystemFlowStatisticsDto();
        String sqlFlowTypeMonth = "SELECT " +
                "SUBSTR(CREATEDATE, 0, 7) yearmonth, " +
                "workflowname workflowName, " +
                "count(DISTINCT REQ_ID) as count " +
                "from ECOLOGY_TARGET." +
                "V_GW_SYSTEM_RELATED_FLOW v " +
                "where 1=1 " +
                "and (v.xtbh in (:systemClassNoList)) " +
                "and (:beginDate is null or v.CREATEDATE >= :beginDate) " +
                "and (:endDate is null or v.CREATEDATE <= :endDate) " +
                "GROUP BY SUBSTR(CREATEDATE, 0, 7), workflowname " +
                "ORDER BY yearmonth ASC ";

        String sqlFlowType = "SELECT " +
                "workflowname workflowName, " +
                "count(DISTINCT REQ_ID) as count " +
                "from ECOLOGY_TARGET." +
                "V_GW_SYSTEM_RELATED_FLOW v " +
                "where 1=1 " +
                "and (v.xtbh in (:systemClassNoList)) " +
                "and (:beginDate is null or v.CREATEDATE >= :beginDate) " +
                "and (:endDate is null or v.CREATEDATE <= :endDate) " +
                "GROUP BY " +
                "workflowname ";

        String sqlSysChangeFlow = "SELECT bgjb changeLevel,count(DISTINCT REQ_ID) AS count " +
                "from ECOLOGY_TARGET." +
                "V_GW_SYSTEM_RELATED_FLOW v " +
                "where 1=1 " +
                "and (v.xtbh in (:systemClassNoList)) " +
                "and (:beginDate is null or v.CREATEDATE >= :beginDate) " +
                "and (:endDate is null or v.CREATEDATE <= :endDate) " +
                "and WORKFLOWNAME='09.系统变更审批流程' " +
                "GROUP by bgjb ";

        String sqlSysChangeTestFlow = "SELECT bgjb changeLevel,count(DISTINCT REQ_ID) AS count " +
                "from ECOLOGY_TARGET." +
                "V_GW_SYSTEM_RELATED_FLOW v " +
                "where 1=1 " +
                "and (v.xtbh in (:systemClassNoList)) " +
                "and (:beginDate is null or v.CREATEDATE >= :beginDate) " +
                "and (:endDate is null or v.CREATEDATE <= :endDate) " +
                "and WORKFLOWNAME='52.系统测试变更流程（自研类）' " +
                "GROUP by bgjb ";

        String sqlChangeFlowLineSingle = "SELECT distinct SUBSTR(CREATEDATE, 0, 7) yearmonth, REQUESTNAME requestName, workflowname workflowName, " +
                "lastname userName, req_id requestId, xtbh systemClassNo, CREATEDATE applyDate, CREATETIME, BGJB changeLevel " +
                "from ECOLOGY_TARGET." +
                "V_GW_SYSTEM_RELATED_FLOW v " +
                "where 1=1 " +
                "and (v.xtbh in (:systemClassNoList)) " +
                "and (:beginDate is null or v.CREATEDATE >= :beginDate) " +
                "and (:endDate is null or v.CREATEDATE <= :endDate) " +
                "and WORKFLOWNAME IN('09.系统变更审批流程','52.系统测试变更流程（自研类）') " +
                "ORDER BY CREATEDATE ASC,CREATETIME ASC ";

        String sqlChangeFlowLineMultiple = "SELECT SUBSTR(CREATEDATE, 0, 7) yearmonth, bgjb changeLevel,count(*) AS count " +
                "from ECOLOGY_TARGET." +
                "V_GW_SYSTEM_RELATED_FLOW v " +
                "where 1=1 " +
                "and (v.xtbh in (:systemClassNoList)) " +
                "and (:beginDate is null or v.CREATEDATE >= :beginDate) " +
                "and (:endDate is null or v.CREATEDATE <= :endDate) " +
                "and WORKFLOWNAME IN('09.系统变更审批流程','52.系统测试变更流程（自研类）') " +
                "GROUP BY SUBSTR(CREATEDATE, 0, 7), bgjb " +
                "ORDER BY yearmonth ASC";

        String sqlSystemTypeMonth = "SELECT " +
                "SUBSTR(CREATEDATE, 0, 7) yearmonth, " +
                "xtbh systemClassNo, " +
                "count(DISTINCT REQ_ID) as count " +
                "from ECOLOGY_TARGET." +
                "V_GW_SYSTEM_RELATED_FLOW v " +
                "where 1=1 " +
                "and (v.xtbh in (:systemClassNoList)) " +
                "and (:beginDate is null or v.CREATEDATE >= :beginDate) " +
                "and (:endDate is null or v.CREATEDATE <= :endDate) " +
                "GROUP BY " +
                "SUBSTR(CREATEDATE, 0, 7), xtbh " +
                "ORDER BY " +
                "yearmonth ASC ";

        String sqlSystemType = "SELECT " +
                "xtbh systemClassNo, " +
                "count(DISTINCT REQ_ID) as count " +
                "from ECOLOGY_TARGET." +
                "V_GW_SYSTEM_RELATED_FLOW v " +
                "where 1=1 " +
                "and (v.xtbh in (:systemClassNoList)) " +
                "and (:beginDate is null or v.CREATEDATE >= :beginDate) " +
                "and (:endDate is null or v.CREATEDATE <= :endDate) " +
                "GROUP BY " +
                "xtbh ";

        //取各流程、各系统流程总数排在前10个的,10名之后的分组到其他，计总数
        String workflowSeqSql = "select * from ( " +
                "select workflowname workflowName, count(distinct req_id) as count " +
                "from ECOLOGY_TARGET.v_gw_system_related_flow v " +
                "where 1=1 " +
                "and (v.xtbh in (:systemClassNoList)) " +
                "and (:beginDate is null or v.CREATEDATE >= :beginDate) " +
                "and (:endDate is null or v.CREATEDATE <= :endDate) " +
                "group by workflowname " +
                "order by count desc " +
                ") tmp where rownum <=10 " +
                "UNION ALL " +
                "SELECT '其他' workflowName, sum(cnt) AS count FROM ( " +
                "SELECT * FROM ( " +
                "select workflowname,cnt, rownum as rn from ( " +
                "select workflowname,count(distinct req_id) as cnt " +
                "from ECOLOGY_TARGET.v_gw_system_related_flow v " +
                "where 1=1 " +
                "and (v.xtbh in (:systemClassNoList)) " +
                "and (:beginDate is null or v.CREATEDATE >= :beginDate) " +
                "and (:endDate is null or v.CREATEDATE <= :endDate) " +
                "group by workflowname " +
                "order by cnt desc " +
                ") tmp " +
                ")WHERE rn > 10) ";

        String systemSeqSql = "select * from ( " +
                "select xtbh systemClassNo, count(distinct req_id) as count " +
                "from ECOLOGY_TARGET.v_gw_system_related_flow v " +
                "where 1=1 " +
                "and (v.xtbh in (:systemClassNoList)) " +
                "and (:beginDate is null or v.CREATEDATE >= :beginDate) " +
                "and (:endDate is null or v.CREATEDATE <= :endDate) " +
                "group by xtbh " +
                "order by count desc " +
                ") tmp where rownum <=10 " +
                "UNION ALL " +
                "SELECT '其他' systemClassNo, sum(cnt) AS count FROM ( " +
                "SELECT * FROM ( " +
                "select xtbh,cnt, rownum as rn from ( " +
                "select xtbh,count(distinct req_id) as cnt " +
                "from ECOLOGY_TARGET.v_gw_system_related_flow v " +
                "where 1=1 " +
                "and (v.xtbh in (:systemClassNoList)) " +
                "and (:beginDate is null or v.CREATEDATE >= :beginDate) " +
                "and (:endDate is null or v.CREATEDATE <= :endDate) " +
                "group by xtbh " +
                "order by cnt desc " +
                ") tmp " +
                ")WHERE rn > 10) ";
        //前十个流程
        List<VITSystemOAFlowStatisticsDto> flowType10List = namedJdbcTemplateEcology.query(workflowSeqSql, paramMap, new BeanPropertyRowMapper<>(VITSystemOAFlowStatisticsDto.class));

        List<VITSystemOAFlowStatisticsDto> flowTypeMonthList = namedJdbcTemplateEcology.query(sqlFlowTypeMonth, paramMap, new BeanPropertyRowMapper<>(VITSystemOAFlowStatisticsDto.class));
        List<VITSystemOAFlowStatisticsDto> flowTypeList = namedJdbcTemplateEcology.query(sqlFlowType, paramMap, new BeanPropertyRowMapper<>(VITSystemOAFlowStatisticsDto.class));
        List<VITSystemOAFlowStatisticsDto> sysChangeFlowList = namedJdbcTemplateEcology.query(sqlSysChangeFlow, paramMap, new BeanPropertyRowMapper<>(VITSystemOAFlowStatisticsDto.class));
        List<VITSystemOAFlowStatisticsDto> sysChangeTestFlowList = namedJdbcTemplateEcology.query(sqlSysChangeTestFlow, paramMap, new BeanPropertyRowMapper<>(VITSystemOAFlowStatisticsDto.class));

        resultDto.setCount10FlowTypeList(flowType10List);
        resultDto.setCountFlowTypeMonthList(flowTypeMonthList);
        resultDto.setCountFlowTypeList(flowTypeList);
        resultDto.setSystemChangeFlowCountList(sysChangeFlowList);
        resultDto.setSystemChangeTestFlowCountList(sysChangeTestFlowList);

        if (size == 1) {
            //只有单个系统情况下需要请求的数据
            List<VITSystemOAFlowStatisticsDto> changeFlowLineSingleList = namedJdbcTemplateEcology.query(sqlChangeFlowLineSingle, paramMap, new BeanPropertyRowMapper<>(VITSystemOAFlowStatisticsDto.class));
            resultDto.setChangeFlowLineSingleList(changeFlowLineSingleList);
        } else {
            //只有多个系统的情况下才需要请求的数据
            //前十个系统
            List<VITSystemOAFlowStatisticsDto> systemType10List = namedJdbcTemplateEcology.query(systemSeqSql, paramMap, new BeanPropertyRowMapper<>(VITSystemOAFlowStatisticsDto.class));
            List<VITSystemOAFlowStatisticsDto> changeFlowLineMulList = namedJdbcTemplateEcology.query(sqlChangeFlowLineMultiple, paramMap, new BeanPropertyRowMapper<>(VITSystemOAFlowStatisticsDto.class));
            List<VITSystemOAFlowStatisticsDto> systemTypeMonthList = namedJdbcTemplateEcology.query(sqlSystemTypeMonth, paramMap, new BeanPropertyRowMapper<>(VITSystemOAFlowStatisticsDto.class));
            List<VITSystemOAFlowStatisticsDto> systemTypeList = namedJdbcTemplateEcology.query(sqlSystemType, paramMap, new BeanPropertyRowMapper<>(VITSystemOAFlowStatisticsDto.class));
            resultDto.setCount10SystemTypeList(systemType10List);
            resultDto.setCountSystemTypeList(systemTypeList);
            resultDto.setCountSystemTypeMonthList(systemTypeMonthList);
            resultDto.setChangeFlowLineMultipleList(changeFlowLineMulList);
        }
        return resultDto;
    }

    public PageDto<VITSystemOAFlowResourceDto> getOAFlowListBySystemClassNo(Integer pageNo, Integer pageSize, String systemClassNo) {
        Pageable pageable = PageRequest.of(pageNo - 1, pageSize);
        Page<VITSystemOAFlowResource> pageRes = vitSystemOAFlowRepository.findBySystemClassNo(pageable, systemClassNo);
        return new PageDto(VITSystemOAFlowResourceConverter.do2Dto(pageRes.getContent()), pageable.getPageNumber(), pageable.getPageSize(), pageRes.getTotalElements());
    }

    public OaRequestLogResp getRequestLogList(Integer requestId, List<String> roleList) throws Exception {
        List<WorkflowRequestlog> logList = workflowRequestlogRepository.findByRequestid(Long.valueOf(requestId));
        if (CollectionUtil.isNotEmpty(logList) && CollectionUtil.isNotEmpty(roleList)) {
            List<String> workflowIds = logList.stream().map(log -> log.getWorkflowid().toString()).collect(Collectors.toList());
            Map<String, Set<String>> authMap = oaFlowAuthorityRepository.findAllByWorkflowIdIn(workflowIds).stream()
                    .filter(auth -> "ROLE".equals(auth.getAuthType()))
                    .collect(Collectors.groupingBy(TOaFlowAuthority::getWorkflowId, Collectors.mapping(TOaFlowAuthority::getAuthValue, Collectors.toSet())));
            Set<String> roleSet = new HashSet<>(roleList);
            logList = logList.stream()
                    .filter(log ->
                            !authMap.containsKey(log.getWorkflowid().toString())
                                    || authMap.get(log.getWorkflowid().toString()).stream().anyMatch(roleSet::contains)
                    )
                    .collect(Collectors.toList());
        }
        List<OaRequestLogResp.OaRequestLog> logs = new ArrayList<>();
        Optional<WorkflowRequestbase> workflowBase = workflowRequestbaseRepository.findByRequestid(requestId);
        Boolean isEnd = workflowBase.isPresent() ? OaWorkFlowRequestTableStatusEnum.isEnd(workflowBase.get().getStatus()) : null;
        AtomicReference<String> maxRequestLogId = new AtomicReference<>();
        ConvertLog2Vo(logList, logs, maxRequestLogId);
        return OaRequestLogResp.builder()
                .isEnd(isEnd)
                .maxrequestlogid(maxRequestLogId.get())
                .loglist(logs)
                .totalCount(CollectionUtils.isEmpty(logs) ? 0 : logs.size()).build();
    }

    private void ConvertLog2Vo(List<WorkflowRequestlog> logList, List<OaRequestLogResp.OaRequestLog> logs, AtomicReference<String> maxRequestLogId) throws Exception {
        if (logList.isEmpty()) {
        } else {
            for (WorkflowRequestlog log : logList) {
                maxRequestLogId.set(String.valueOf(Objects.isNull(maxRequestLogId.get()) ? log.getRequestLogId() : Math.max(log.getRequestLogId(), Long.parseLong(maxRequestLogId.get()))));
                OaRequestLogResp.OaRequestLog logVo = new OaRequestLogResp.OaRequestLog();
                if (Strings.isNotBlank(log.getAnnexdocids())) {
                    List<Long> annexDocIds = Arrays.stream(log.getAnnexdocids().split(",")).map(Long::valueOf).collect(Collectors.toList());
                    if (!CollectionUtils.isEmpty(annexDocIds)) {
                        List<OaRequestLogResp.OaRequestLog.Doc> annexDocList = getDocs(annexDocIds);
                        logVo.setAnnexdocs(annexDocList);
                    }
                }
//                logVo.setCheckSuccess();
//                logVo.setDisplaybyagentname();
                if (Objects.nonNull(log.getOperatorDept())) {
                    logVo.setDisplaydepid(String.valueOf(log.getOperatorDept()));
                    LdapResponse<List<LdapEhrDeptDto>> resp = iFeignLdapService.getDepartmentList(LdapEhrDeptSearchDto.builder().oaDeptIdList(Arrays.asList(Math.toIntExact(log.getOperatorDept()))).build());
                    if (Objects.nonNull(resp) && CollectionUtil.isNotEmpty(resp.getData())) {
                        logVo.setDisplaydepname(resp.getData().get(0).getDtitle());
                    }
                }
                if (Objects.nonNull(log.getHrmresource())) {
                    Hrmresource hrmresource = log.getHrmresource();
                    logVo.setDisplayid(Objects.nonNull(log.getOperator()) ? String.valueOf(log.getOperator()) : null);
                    logVo.setDisplayaccount(hrmresource.getLoginid());
                    logVo.setDisplayname(hrmresource.getLastname());
                    logVo.setDisplaytype(Objects.nonNull(hrmresource.getAccounttype()) ? String.valueOf(hrmresource.getAccounttype()) : null);
                    logVo.setImg_path(hrmresource.getMessagerurl());
                }
//                logVo.setForward();
//                logVo.setForwardMenuName();
//
//                log.getHandWrittenSign() 记录id，需要再去查找未知表
//                logVo.setHandWrittenSignDoc();
//
//                logVo.setIsProtected();
//                logVo.setIsReference();
//                logVo.setIsexsAgent();
//                logVo.setIsinneruser();
//
//                logVo.setLog_agentorbyagentid();

                if (Objects.nonNull(log.getNodeid())) {
                    Optional<WorkflowNodeBase> node = workflowNodeBaseRepo.findById(log.getNodeid());
                    if (node.isPresent()) {
                        logVo.setLog_nodename(node.get().getNodeName());
                    }
                }
                logVo.setLog_operatedate(Objects.nonNull(log.getOperatedate()) ? String.valueOf(log.getOperatedate()) : null);
                logVo.setLog_operatetime(Objects.nonNull(log.getOperatetime()) ? String.valueOf(log.getOperatetime()) : null);
                logVo.setLog_remarkHtml(log.getRemark());
                logVo.setLogid(Objects.nonNull(log.getLOGID()) ? String.valueOf(log.getLOGID()) : null);
                logVo.setLogtype(Objects.nonNull(log.getLogtype()) ? String.valueOf(log.getLogtype().getValue()) : null);

                logVo.setOperationname(Objects.nonNull(log.getLogtype()) ? log.getLogtype().getDesc() : null);

//                logVo.setPgflag();

                logVo.setReceiveUser(log.getReceivedPersons());

                logVo.setReceiveUserCount(Strings.isNotBlank(log.getReceivedPersons()) ? log.getReceivedPersons().split(",").length : 0);

                logVo.setRemarkLocation(log.getRemarkLocation());
                //文档
                if (Strings.isNotBlank(log.getSigndocids())) {
                    List<Long> signDocs = Arrays.stream(log.getSigndocids().split(",")).map(Long::valueOf).collect(Collectors.toList());
                    if (!CollectionUtils.isEmpty(signDocs)) {
                        List<OaRequestLogResp.OaRequestLog.Doc> signDocList = getDocs(signDocs);
                        logVo.setSigndocs(signDocList);
                    }
                }
//                logVo.setSignnaturImgHeight();
                if (Strings.isNotBlank(log.getSignworkflowids())) {
                    //关联流程id
                    List<Integer> signWfIds = Arrays.stream(log.getSignworkflowids().split(",")).map(Integer::valueOf).collect(Collectors.toList());
                    if (!CollectionUtils.isEmpty(signWfIds)) {
                        List<WorkflowRequestbase> signWfList = workflowRequestbaseRepository.findAllByRequestidIn(signWfIds);
                        //流程
                        logVo.setSignwfs(signWfList.stream()
                                .map(s -> OaRequestLogResp.OaRequestLog.SignWorkflow.builder()
                                        .title(s.getRequestname())
                                        .desrequestid(Objects.nonNull(log.getRequestid()) ? String.valueOf(log.getRequestid()) : null)
                                        .requestid(Objects.nonNull(s.getRequestid()) ? String.valueOf(s.getRequestid()) : null)
                                        .build())
                                .collect(Collectors.toList()));
                    }
                }
//                logVo.setSpeechAttachmetnDatas();
//                logVo.setTempremark();
//                logVo.setUserimg();
                logs.add(logVo);
            }
        }
    }

    private List<OaRequestLogResp.OaRequestLog.Doc> getDocs(List<Long> docIds) {
        List<DocImageFile> docs = docImageFileRepository.findAllByDOCIDIn(docIds);
        return docs.stream().map(f ->
                OaRequestLogResp.OaRequestLog.Doc.builder()
                        .docImagefileSize(f.getIMAGEFIELSIZE())
                        .docImagefileid(String.valueOf(f.getIMAGEFILEID()))
                        .docImagefilename(f.getIMAGEFILENAME())
                        .showid(String.valueOf(f.getDOCID()))
                        .versionId(f.getVERSIONID())
                        .build()
        ).collect(Collectors.toList());
    }

    /**
     * 如有新增的workflowid，增加对应的配置
     *
     * @param workFlowRelList
     */
    @Transactional
    public void updateWorkFlowConfig(List<WorkFlowRelation> workFlowRelList) {
        List<TOaFlowMetaTable> config = tOaFlowMetaTableRepository.getAllFlow();
        List<String> configWorflowIds = config.stream().map(TOaFlowMetaTable::getWorkFlowId).collect(Collectors.toList());
        for (WorkFlowRelation relation : workFlowRelList) {
            List<String> workFlowIdsRelation = Arrays.asList(relation.getWorkflowId().split(","));
            List<String> cloneWorkFlowIdsRelation = JSON.parseObject(JSON.toJSONString(workFlowIdsRelation), new TypeReference<List<String>>() {
            });
            //看看是否存在最新配置的表名
            String tableName = relation.getFormId() != null ? relation.getFormId().abs().toPlainString() : "";
            tableName = "FORMTABLE_MAIN_" + tableName;
            //检查是否有不存在的配置
            List<String> unconfigWorkFlowIds = workFlowIdsRelation.stream().filter(e -> !configWorflowIds.contains(e)).collect(Collectors.toList());
            if (CollectionUtil.isNotEmpty(unconfigWorkFlowIds)) {
                //发现有不存在配置的工作流，则取一个已经配置好的工作流
                cloneWorkFlowIdsRelation.removeAll(unconfigWorkFlowIds);
                if (CollectionUtil.isNotEmpty(cloneWorkFlowIdsRelation)) {
                    //说明之前存在配置
                    List<TOaFlowMetaTable> configs = tOaFlowMetaTableRepository.getById(cloneWorkFlowIdsRelation.get(0));
                    List<TOaFlowRelativeMetaTable> configTable = tOaFlowRelativeMetaTableRepository.findByWorkFlowId(cloneWorkFlowIdsRelation.get(0));
                    List<TOaFlowSystemTable> configMq = tOaFlowSystemTableRepository.getSystemName(cloneWorkFlowIdsRelation.get(0));
                    String finalTableName = tableName;
                    unconfigWorkFlowIds.forEach(
                            unconfigWorkFlowId -> {
                                //保存字段
                                List<TOaFlowMetaTable> updatedConfigsNew = new ArrayList<>();
                                configs.forEach(c -> {
                                    c.setId(null);
                                    c.setWorkFlowId(unconfigWorkFlowId);
                                    c.setFlowName(relation.getProdWorkflowName());
                                    TOaFlowMetaTable target = new TOaFlowMetaTable();
                                    BeanUtil.copyProperties(c, target);
                                    updatedConfigsNew.add(target);
                                });
                                tOaFlowMetaTableRepository.saveAllAndFlush(updatedConfigsNew);
                                //保存对应oa表
                                if (CollectionUtil.isNotEmpty(configTable)) {
                                    configTable.forEach(c -> {
                                                TOaFlowRelativeMetaTable targert = new TOaFlowRelativeMetaTable();
                                                BeanUtil.copyProperties(c, targert);
                                                targert.setWorkFlowId(unconfigWorkFlowId);
                                                logger.info("new table", finalTableName);
                                                //length() >15即可代表新表不是空
                                                if (!c.getWorkFlowTable().toUpperCase().equals(finalTableName) && finalTableName.length() > 15) {
                                                    targert.setWorkFlowDetailTable(targert.getWorkFlowDetailTable().toUpperCase().replaceAll(c.getWorkFlowTable().toUpperCase(), finalTableName));
                                                    targert.setWorkFlowTable(finalTableName);
                                                }
                                                targert.setId(null);
                                                tOaFlowRelativeMetaTableRepository.saveAndFlush(targert);
                                            }
                                    );
                                } else {
                                    logger.error(cloneWorkFlowIdsRelation.get(0) + "配置table为空");
                                }
                                //保存对应mq表
                                if (CollectionUtil.isNotEmpty(configMq) && configMq.size() == 1) {
                                    configMq.forEach(c -> {
                                        TOaFlowSystemTable targert = new TOaFlowSystemTable();
                                        BeanUtil.copyProperties(c, targert);
                                        targert.setWorkFlowId(unconfigWorkFlowId);
                                        targert.setId(null);
                                        tOaFlowSystemTableRepository.saveAndFlush(targert);
                                    });
                                } else {
                                    logger.info(cloneWorkFlowIdsRelation.get(0) + "配置mq存在多条或为空" + configMq.size());
                                }
                                logger.info(relation.getProdWorkflowName() + "更新。" + unconfigWorkFlowId + "的配置来自于" + cloneWorkFlowIdsRelation.get(0));
                            }
                    );
                } else {
                    logger.info(relation.getProdWorkflowName() + "从未配置过");
                }
            }
        }
    }

    public Object getOaNoContractApproval(OaParaDto param) {
        String config = systemConfigService.findConfigValueByCode(DictConstant.OA_NO_CONTRACT_APPROVAL);
        String moneyOpera = systemConfigService.findConfigValueByCode(DictConstant.OA_MONEY_OPEPA);
        String sql = "SELECT" +
                " y.REQUESTNAMENEW ," +
                " x.SQRQ ," +
                " x.SQR ," +
                " Z.LASTNAME ," +
                " x.YYSM" +
                " FROM" +
                " ECOLOGY_TARGET." + config + " x" +
                " LEFT JOIN ECOLOGY_TARGET.WORKFLOW_REQUESTBASE y ON" +
                " x.REQUESTID = y.REQUESTID" +
                " LEFT JOIN ECOLOGY_TARGET.HRMRESOURCE Z ON x.SQR = Z.ID " +
                " WHERE" +
                " X.SQRBM = " + moneyOpera +
                " AND X.SQRQ >= ?" +
                " AND X.SQRQ <= ?";
        List argList = new ArrayList();
        argList.addAll(Arrays.asList(new String[]{param.getApplyDateBegin(), param.getApplyDateEnd()}));
        if (param.getApplicant() != null) {
            argList.add(Integer.parseInt(getUserId(param.getApplicant())));
            sql += " AND X.SQR = ?";
        }
        sql += " ORDER BY x.SQRQ ASC";
        List<Map<String, Object>> list = oracleJdbcTemplate.queryPre(sql, argList.toArray());
        return list;
    }

    public Object getOaContract(OaParaDto param) {
        String config = systemConfigService.findConfigValueByCode(DictConstant.OA_CONTRACT);
        String moneyOpera = systemConfigService.findConfigValueByCode(DictConstant.OA_MONEY_OPEPA);
        String sql = "SELECT" +
                " y.REQUESTNAMENEW ," +
                " x.SQRQ ," +
                " x.SQR ," +
                " Z.LASTNAME ," +
                " x.HTYXKSRQ," +
                " x.HTYXJSRQ" +
                " FROM" +
                " ECOLOGY_TARGET." + config + " x" +
                " LEFT JOIN ECOLOGY_TARGET.WORKFLOW_REQUESTBASE y ON" +
                " x.REQUESTID = y.REQUESTID" +
                " LEFT JOIN ECOLOGY_TARGET.HRMRESOURCE Z ON x.SQR = Z.ID " +
                " WHERE" +
                " X.SQRBM = " + moneyOpera +
                " AND X.SQRQ >= ?" +
                " AND X.SQRQ <= ?";
        List argList = new ArrayList();
        argList.addAll(Arrays.asList(new String[]{param.getApplyDateBegin(), param.getApplyDateEnd()}));
        if (param.getApplicant() != null) {
            argList.add(Integer.parseInt(getUserId(param.getApplicant())));
            sql += " AND X.SQR = ?";
        }
        sql += " ORDER BY x.SQRQ ASC";
        List<Map<String, Object>> list = oracleJdbcTemplate.queryPre(sql, argList.toArray());
        return list;
    }

    public Object getOaInnerStampApproval(OaParaDto param) {
        String config = systemConfigService.findConfigValueByCode(DictConstant.OA_INNER_STAMP_APPROVAL);
        String moneyOpera = systemConfigService.findConfigValueByCode(DictConstant.OA_MONEY_OPEPA);
        String sql = "SELECT" +
                " y.REQUESTNAMENEW ," +
                " x.SQDH ," +
                " x.SQRQ ," +
                " x.SQR ," +
                " Z.LASTNAME ," +
                " x.SM ," +
                " x.SFHTXY" +
                " FROM" +
                " ECOLOGY_TARGET." + config + " x" +
                " LEFT JOIN ECOLOGY_TARGET.WORKFLOW_REQUESTBASE y ON" +
                " x.REQUESTID = y.REQUESTID   " +
                " LEFT JOIN ECOLOGY_TARGET.HRMRESOURCE Z ON x.SQR = Z.ID " +
                " WHERE" +
                " X.SQRBM = " + moneyOpera +
                " AND X.SQRQ >= ?" +
                " AND X.SQRQ <= ?";
        List argList = new ArrayList();
        argList.addAll(Arrays.asList(new String[]{param.getApplyDateBegin(), param.getApplyDateEnd()}));
        if (param.getApplicant() != null) {
            argList.add(Integer.parseInt(getUserId(param.getApplicant())));
            sql += " AND X.SQR = ?";
        }
        sql += " ORDER BY x.SQRQ ASC";
        List<Map<String, Object>> list = oracleJdbcTemplate.queryPre(sql, argList.toArray());
        return list;
    }

    public Object getOaMoneyTransfer(OaParaDto param) {
        String config = systemConfigService.findConfigValueByCode(DictConstant.OA_MONEY_TRANSFER);
        //都是股衍的流程，不需要限定部门
//        String department = systemConfigService.findConfigValueByCode(DictConstant.OA_EQUITY_DERIVATE);
        String sql = "SELECT" +
                " y.REQUESTNAMENEW ," +
                " x.* ," +
                " Z.LASTNAME ," +
                " A.* " +
                " FROM" +
                " ECOLOGY_TARGET." + config + " x" +
                " LEFT JOIN ECOLOGY_TARGET." + config + "_DT1 A ON A.MAINID = x.ID" +
                " LEFT JOIN ECOLOGY_TARGET.WORKFLOW_REQUESTBASE y ON" +
                " x.REQUESTID = y.REQUESTID   " +
                " LEFT JOIN ECOLOGY_TARGET.HRMRESOURCE Z ON x.SQR = Z.ID " +
                " WHERE" +
//                " X.SQRBM = " +department +
                "  X.SQRQ >= ?" +
                " AND X.SQRQ <= ?";
        List argList = new ArrayList();
        argList.addAll(Arrays.asList(new String[]{param.getApplyDateBegin(), param.getApplyDateEnd()}));
        if (param.getApplicant() != null) {
            argList.add(Integer.parseInt(getUserId(param.getApplicant())));
            sql += " AND X.SQR = ?";
        }
        sql += " ORDER BY x.SQRQ ASC";
        List<Map<String, Object>> list = oracleJdbcTemplate.queryPre(sql, argList.toArray());
        return list;
    }


    public Object getOaFundBuy(OaParaDto param) {
        String config = systemConfigService.findConfigValueByCode(DictConstant.OA_FUND_BUY);
        //都是股衍的流程，不需要限定部门
//        String department = systemConfigService.findConfigValueByCode(DictConstant.OA_EQUITY_DERIVATE);
        String sql = "SELECT" +
                " y.REQUESTNAMENEW ," +
                " x.* ," +
                " Z.LASTNAME ," +
                " A.*, " +
                " B.* " +
                "  FROM" +
                " ECOLOGY_TARGET." + config + " x" +
                " LEFT JOIN ECOLOGY_TARGET." + config + "_DT1 A ON A.MAINID = x.ID" +
                " LEFT JOIN ECOLOGY_TARGET." + config + "_DT2 B ON B.MAINID = x.ID" +
                " LEFT JOIN ECOLOGY_TARGET.WORKFLOW_REQUESTBASE y ON" +
                " x.REQUESTID = y.REQUESTID   " +
                " LEFT JOIN ECOLOGY_TARGET.HRMRESOURCE Z ON x.SQR = Z.ID " +
                " WHERE" +
//                " X.SQRBM = " +department +
                " X.SQRQ >= ?" +
                " AND X.SQRQ <= ?";
        List argList = new ArrayList();
        argList.addAll(Arrays.asList(new String[]{param.getApplyDateBegin(), param.getApplyDateEnd()}));
        if (param.getApplicant() != null) {
            argList.add(Integer.parseInt(getUserId(param.getApplicant())));
            sql += " AND X.SQR = ?";
        }
        sql += " ORDER BY x.SQRQ ASC";
        List<Map<String, Object>> list = oracleJdbcTemplate.queryPre(sql, argList.toArray());
        return list;
    }

    public Object getOaDepartmentByEhrDepartment(String deptId, String companyId) {
        String sql = "";
        List argList = new ArrayList();
        if (StringUtils.isNotBlank(deptId)) {
            sql = "SELECT x.ID AS \"DEPARTMENTID\", x.DEPARTMENTMARK AS \"DEPARTMENTNAME\", x.SUBCOMPANYID1 AS \"COMPANYID\" FROM ECOLOGY_TARGET.HRMDEPARTMENT x WHERE x.OUTKEY = ?";
            argList.addAll(Arrays.asList(new String[]{deptId}));
        } else {
            sql = "SELECT Y.ID AS \"COMPANYID\",Y.SUBCOMPANYDESC AS \"COMPANYNAME\" FROM ECOLOGY_TARGET.HRMSUBCOMPANY Y WHERE Y.OUTKEY = ? ";
            argList.addAll(Arrays.asList(new String[]{companyId}));
        }
        List<Map<String, Object>> list = oracleJdbcTemplate.queryPre(sql, argList.toArray());
        return list;
    }

    public List<Map<String, String>> getRequestDataOrigin(String workflowId, String requestId, String flowStatus) {
        List<TOaFlowRelativeMetaTable> workFlowList = tOaFlowRelativeMetaTableRepository.findByWorkFlowId(workflowId);
        if (CollectionUtils.isEmpty(workFlowList)) {
            return Collections.EMPTY_LIST;
        }
        TOaFlowRelativeMetaTable t = workFlowList.get(0);
        //取得主表名和明细表名
        String table = t.getWorkFlowTable();
        String detailTable = t.getWorkFlowDetailTable();
        String detailMapConvertName = t.getDetailMapConvertName();

        //获取属性下拉框的属性集
        Map<String, Map<Integer, String>> selection = getSelectionNewByFlowId(workflowId);

        List<Map<String, String>> finalList = new ArrayList<>();
        finalList.add(getRequestInfoOutMap(Integer.valueOf(workflowId), Integer.valueOf(requestId), table, detailTable, selection, flowStatus, detailMapConvertName));
        return finalList;
    }

    private Map getRequestInfoOutMap(Integer workflowId, Integer requestId, String table, String detailTable, Map<String, Map<Integer, String>> selection, String flowStatus, String detailMapConvertName) {
        //检查request是否在OA系统存在
        Optional<WorkflowRequestbase> workflowRequestbase = workflowRequestbaseRepository.findByRequestid(requestId);
        if (!workflowRequestbase.isPresent()) {
            throw new OaflowException("没有在OA系统WorkflowRequestbase表中获取到requestId为" + requestId + "的记录",false);
        }
        //没传flowStatus这个参数也能过
        if ((flowStatus != null && !("").equals(flowStatus.trim())) && flowStatus.equals(workflowRequestbase.get().getCurrentnodetype())) {//限定特定状态
            throw new OaflowException("requestId为：" + requestId + "的流程在OA尚未结束");
        }

        Map<String, Object> ret = new HashMap<String, Object>();
        ret.put(OaflowResponse.requestId, requestId);
        ret.put(OaflowResponse.flowStatusType, workflowRequestbase.get().getCurrentnodetype());
        ret.put(OaflowResponse.flowNode, workflowRequestbase.get().getStatus());
        ret.put(OaflowResponse.flowNodeCode, workflowRequestbase.get().getCurrentnodeid());
        ret.put(OaflowResponse.workFlowName, workflowRequestbase.get().getRequestnamenew());

        //从OA获取主元素信息
        Map<String, Object> oaItems = getItemsFromOA(requestId, table.toUpperCase(), detailTable);

        StringBuffer sql = new StringBuffer();
        StringBuffer dsql = new StringBuffer();

        //替换下拉框的内容
        logger.info("进行下拉框内容转换，字典转换成value");
        //YWLS 0,1,2,3,4
        Map<String, Object> tmpselect = new HashMap<>();
        //ywlx 0-xxx 1-yyy
        for (Map.Entry<String, Map<Integer, String>> entry : selection.entrySet()) {
            //ywlx
            String oaItemkey = entry.getKey();
            //0-xxx 1-yyy
            Map<Integer, String> selectMap = entry.getValue();
            if (oaItems.containsKey(oaItemkey.toUpperCase())) {
                //元素属性是下拉框的
                if (oaItems.get(oaItemkey.toUpperCase()) != null) {
                    //对于多选值的处理
                    String selects[] =oaItems.get(oaItemkey.toUpperCase()).toString().split(",");
                    List<String> newValList = new ArrayList();
                    if (selects != null && selects.length > 0){
                        for (String select:selects) {
                            newValList.add(selectMap.get(Integer.parseInt(select)));
                        }
                    }
                    //取得下拉框 字典值 对应的 中文
                    String newVal = String.join(",",newValList);
//                    String newVal = selectMap.get(Integer.parseInt(oaItems.get(oaItemkey.toUpperCase()).toString()));
                    //同时存储对应的字典值
                    tmpselect.put(oaItemkey.toUpperCase(), oaItems.get(oaItemkey.toUpperCase()).toString());

                    oaItems.put(oaItemkey.toUpperCase(), newVal);
                }
            }
        }
        //字段名称转换
        logger.info("将OA字段名称转换为网关字段名称");
        Map<String, Object> afterCvtOaItems = new LinkedHashMap<String, Object>();
//        List<TOaFlowMetaTable> metas = tOaFlowMetaTableRepository.findByWorkFlowId("" + workflowId);
//        if (CollectionUtils.isEmpty(metas)) {
//            throw new OaflowException("没有获取到workflowId=" + workflowId + "的TOaFlowMetaTable表记录，请检查Mysql-TOaFlowMetaTable配置信息");
//        }
//        Map<String, String> convertMap = getConvertMap(workflowId);
//        convertMap.put("ID", "ID");
//        convertMap.put("SQDH", "applyNumber");
        for (Map.Entry<String, Object> entry : oaItems.entrySet()) {
            String key = entry.getKey();
            Object val = entry.getValue();
            if (val == null) {
                continue;
            }
//            if (convertMap.containsKey(key)) {
//                afterCvtOaItems.put(convertMap.get(key), val);
//                //如果是字典，那么也put一个字典
//                if (tmpselect.containsKey(key)) {
//                    afterCvtOaItems.put(convertMap.get(key) + "Code", tmpselect.get(key));
//                }
//            } else {
//                logger.info("TOaFlowMetaTable表中没有配置workflowId={}的{}字段转换配置，查看是不是必须配置。", workflowId, key);
            afterCvtOaItems.put(key, val);
            if (tmpselect.containsKey(key)) {
                afterCvtOaItems.put(key + "Code", tmpselect.get(key));
            }
//            }
        }

        if (afterCvtOaItems.size() <= 0) {
            throw new OaflowException("查询结果为NULL");
        }
        int id = Integer.parseInt(afterCvtOaItems.get("ID").toString());

        List<Map<String, Object>> detailRows = new ArrayList<>();
        if (!table.equals(detailTable)) {
            if (!detailTable.contains(",")) {
                dsql.append("select * ");
//                for (TOaFlowMetaTable map : mapList) {
//                    if (map.getFieldType().equals("1"))
//                        dsql.append(map.getFlowFieldName().toUpperCase() + " as \"" + map.getInterfaceFieldName() + "\",");
//                }
//                dsql.deleteCharAt(dsql.length() - 1);
                dsql.append(" from ECOLOGY_TARGET." + detailTable.toUpperCase() + " where MAINID=" + "'" + id + "'");

                logger.info(dsql.toString());

                detailRows = oracleJdbcTemplate.query(dsql.toString());
                //下拉框转换
//                Map<String, String> mapRelation = mapList.stream().collect(Collectors.toMap(TOaFlowMetaTable::getInterfaceFieldName, TOaFlowMetaTable::getFlowFieldName, (old, cur) -> old));
                convertSelectItemWithoutMap(selection, detailRows);
                if (detailRows.size() > 0) {
                    String detailMapName = "detailMap";
                    if (StringUtils.isNotBlank(detailMapConvertName)) {
                        detailMapName = detailMapConvertName;
                    }
                    afterCvtOaItems.put(detailMapName, detailRows);

                }
            } else {
                //处理多个DT的情况
                String[] detailTables = detailTable.split(",");
                String[] detailConvertName = StringUtils.isNotBlank(detailMapConvertName) ? detailMapConvertName.split(",") : null;
//                Map<String, String> mapRelation = mapList.stream().collect(Collectors.toMap(TOaFlowMetaTable::getInterfaceFieldName, TOaFlowMetaTable::getFlowFieldName, (old, cur) -> old));
                for (int i = 0; i < detailTables.length; i++) {
                    String de = detailTables[i];
                    String convertName = detailConvertName != null ? detailConvertName[i] : null;
                    //取得DT几
                    String index = de.substring(de.length() - 1);
                    //取得DT表名
                    StringBuffer ddsql = new StringBuffer();
                    ddsql.append("select * ");
//                    for (TOaFlowMetaTable map : mapList) {
//                        if ((map.getFieldType().equals("1")) && (index.equals(map.getDetailIndex()))) {
//                            ddsql.append(map.getFlowFieldName().toUpperCase() + " as \"" + map.getInterfaceFieldName() + "\",");
//                        }
//                    }
//                    ddsql.deleteCharAt(ddsql.length() - 1);
                    ddsql.append(" from ECOLOGY_TARGET." + de.toUpperCase() + " where MAINID=" + "'" + id + "'");

                    logger.info(ddsql.toString());

                    detailRows = oracleJdbcTemplate.query(ddsql.toString());
                    //下拉框转换
                    convertSelectItemWithoutMap(selection, detailRows);
                    if (detailRows.size() > 0) {
                        String detailMapName = "detailMap" + index;
                        if (StringUtils.isNotBlank(detailMapConvertName)) {
                            detailMapName = convertName;
                        }
                        afterCvtOaItems.put(detailMapName, detailRows);
                    }
                }
            }
        }

//        String nameAndEmail = getAppName(afterCvtOaItems.get("applicant").toString());
//        afterCvtOaItems.put("applicant", nameAndEmail);
//
//        if (nameAndEmail != null && nameAndEmail.contains(",") && nameAndEmail.contains("@")) {
//            afterCvtOaItems.put("applicantAccount", nameAndEmail.split(",")[1].split("@")[0]);
//        }
//
//        afterCvtOaItems.put("applicantDepartId", afterCvtOaItems.get("applicantDepart").toString());
//        afterCvtOaItems.put("applicantCompId", afterCvtOaItems.get("applicantComp").toString());
//
//        afterCvtOaItems.put("applicantDepart", getDepart(afterCvtOaItems.get("applicantDepart").toString()));
//        afterCvtOaItems.put("applicantComp", getComp(afterCvtOaItems.get("applicantComp").toString()));

//        if(rows.get("appendix") != null) {
//            rows.put("appendix", getDoc(rows.get("appendix").toString()));
//        }

//        for (TOaFlowMetaTable map : mapList) {
//            String interfaceFieldName = map.getInterfaceFieldName();
//            if (interfaceFieldName.equals("applicant") || interfaceFieldName.equals("applicantDepart") || interfaceFieldName.equals("applicantComp")) {
//                continue;
//            }
//            if (map.getFieldType().equals("0")) {
//                fieldNameMapping(afterCvtOaItems, map, selection);
//            } else if (afterCvtOaItems.containsKey("detailMap") && map.getFieldType().equals("1")) {
//                for (Map dRows : detailRows) {
//                    fieldNameMapping(dRows, map, selection);
//                }
//            } else if (!CollectionUtils.isEmpty(getDetailMapName(afterCvtOaItems))) {
//                for (String s : getDetailMapName(afterCvtOaItems)) {
//                    //detailMap1 contains 1,2,3
//                    if (s.contains(map.getDetailIndex())) {
//                        for (Map dRows : (List<Map<String, Object>>) afterCvtOaItems.get(s)) {
//                            fieldNameMapping(dRows, map, selection);
//                        }
//                    }
//                }
//            }
//        }
        ret.put("flowInfos", afterCvtOaItems);
        return ret;
    }

    private void convertSelectItemWithoutMap(Map<String, Map<Integer, String>> selection, List<Map<String, Object>> detailRows) {
        try {
            if (CollectionUtil.isNotEmpty(selection) && CollectionUtil.isNotEmpty(detailRows)) {
                List<Map<String, Object>> detailRowsNew = new ArrayList<>();
                for (Object o : detailRows) {
                    Map<String, String> m = (Map<String, String>) o;
                    Map<String, Object> newMap = new HashMap<>();
                    newMap.putAll(m);
                    Iterator<Map.Entry<String, String>> it = m.entrySet().iterator();
                    while (it.hasNext()) {
                        Map.Entry<String, String> entry = it.next();
                        String key = entry.getKey();
                        if (selection.keySet().contains(key)) {
                            Map<Integer, String> dic = selection.get(key);
                            Integer intValue = Integer.valueOf(String.valueOf(m.get(key)));
                            String value = dic.get(intValue);
                            newMap.put(key + "SelectName", value);
                        }
                    }
                    detailRowsNew.add(newMap);
                }
                detailRows.clear();
                detailRows.addAll(detailRowsNew);
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("转换下拉值错误", e);
        }
    }

    public Object getWCBBZJB(String qqid) {
        String sql = "SELECT * from ECOLOGY_TARGET.view_wcbbzjb x where x.qqid=? ";
        List argList = new ArrayList();
        argList.add(qqid);
        List<Map<String, Object>> list = oracleJdbcTemplate.queryPre(sql, argList.toArray());
        return list;
    }
}